from common import ParameterEntity,SearchFilter
import DatabaseConnection

class ParameterData(object):
    def __init__(self):
        pass

    def InsertParameter(self,parameter):
        ID = None
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsParameter',(parameter.ParamName,parameter.ParamType,parameter.ParamTypeDesc))
        ID = cursor.fetchone()[0]
        cursor.close()
        conn.commit()
        conn.close()
        return ID

    def UpdateParameter(self,parameter):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdParameter',(parameter.ParamID,parameter.ParamName,parameter.ParamType,parameter.ParamTypeDesc))
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteParameter(self,ID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelParameter',(ID,))
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type ParameterEntity or None (if there is no record with this ID)
    def SelectParameterByID(self,ID):
        query = """ SELECT ParamID,ParamName,ParamType,ParamTypeDesc
                    FROM vwParameter
                    WHERE Username='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return ParameterEntity.ParameterEntity(paramID=result[0][0],paramName=result[0][1],paramType=result[0][2],paramTypeDesc=result[0][3])
        else:
            return None

    #This method returns a list of objects of type ParameterEntity
    def SelectParameterByFilter(self,searchFilter):
        query = """ SELECT ParamID,ParamName,ParamType,ParamTypeDesc
                    FROM vwParameter"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(ParameterEntity.ParameterEntity(paramID=record[0],paramName=record[1],paramType=record[2],paramTypeDesc=record[3]))
        return recordSet

    def SelectAllParameters(self):
        query = """ SELECT ParamID,ParamName,ParamType,ParamTypeDesc
                    FROM vwParameter"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(ParameterEntity.ParameterEntity(paramID=record[0],paramName=record[1],paramType=record[2],paramTypeDesc=record[3]))
        return recordSet
